import logging

from flask import Blueprint, jsonify, request

from mysql_tool.mysql_tool import MySQLTool

smt_bp = Blueprint('smt', __name__)

logging.basicConfig(level=logging.INFO)
logger = logging.getLogger(__name__)

from datetime import datetime


def get_smt(mysql_tool, serial_number):
    """根据流水号查询对应的物料信息"""
    logger.info(f"查询流水号对应的物料信息: {serial_number}")

    # 使用参数化查询防止SQL注入
    smt_sql = """
SELECT
  smt_npm_scan_batch_material_relation.id,
  smt_npm_scan_batch_material_relation.batch_code,
  smt_npm_scan_batch_material_relation.material_id,
  smt_npm_scan_batch_material_relation.reel_id,
  smt_npm_scan_batch_material_relation.machine_id,
  smt_npm_scan_batch_material_relation.slot,
  smt_npm_scan_batch_material_relation._table,
  smt_npm_scan_batch_material_relation.wo_code,
  smt_npm_scan_batch_material_relation.sfc_no,
  smt_npm_scan_batch_material_relation.product_id,
  smt_npm_scan_batch_material_relation.use_time
FROM
  smt_npm_scan_batch_material_relation
WHERE
  smt_npm_scan_batch_material_relation.batch_code = (SELECT a.batch_code FROM smt_npm_scan_record AS a WHERE a.serial_number = %s  LIMIT 1)
              """

    logger.info(f"执行的SQL语句: {smt_sql}")

    # 执行查询
    results = mysql_tool.execute_query(smt_sql, (serial_number,))

    # 处理结果集
    smt_data = []
    for row in results:
        use_time = row[10]  # use_time在结果集中的位置
        if isinstance(use_time, datetime):
            use_time = use_time.isoformat()
        elif use_time:
            use_time = str(use_time)

        item = {
            "id": row[0],
            "batchCode": row[1],
            "materialId": row[2],
            "reelId": row[3],
            "machineId": row[4],
            "slot": row[5],
            "table": row[6],
            "woCode": row[7],
            "sfcNo": row[8],
            "productId": row[9],
            "useTime": use_time
        }
        smt_data.append(item)

    logger.info(f"查询到 {len(smt_data)} 条物料记录")
    return smt_data


@smt_bp.route('/query_smt', methods=['POST'])
def query_smt():
    """通过流水号查询对应的物料信息"""
    try:
        # 获取请求中的JSON数据
        request_data = request.get_json()
        if not request_data:
            return jsonify({"success": False, "message": "请求数据不能为空"}), 400

        # 获取流水号
        serial_number = request_data.get('serialNumber')

        if not serial_number:
            return jsonify({"success": False, "message": "流水号不能为空"}), 400

        logger.info(f"收到查询请求，流水号: {serial_number}")

        # 创建MySQL工具实例
        mysql_tool = MySQLTool()

        # 确保连接数据库
        if not mysql_tool.connect():
            return jsonify({"success": False, "message": "数据库连接失败"}), 500

        try:
            # 查询物料信息
            smt_data = get_smt(mysql_tool, serial_number)

            # 返回结果
            return jsonify({
                "success": True,
                "data": smt_data
            })

        finally:
            # 确保断开数据库连接
            mysql_tool.disconnect()

    except Exception as e:
        logger.error(f"查询物料信息失败: {str(e)}")
        return jsonify({
            "success": False,
            "message": f"查询失败: {str(e)}"
        }), 500